-- @owner: songjing20
-- @date: 2024-7-25
-- @testpoint: 兼容模式为B，且打开参数打开参数enable_set_variable_b_format，触发器中设置自定义变量成功

--连接B库，设置参数
@conn PrimaryDbBmode;
show sql_compatibility;
set enable_set_variable_b_format to on;

--step1:建表;expect:成功
drop table if exists t_trigger_0056;
create table t_trigger_0056(a int);

--step2:触发器中设置自定义变量;expect:插入成功，并且@num的值变为100、300
drop function if exists func_trigger_0056;
create or replace function func_trigger_0056() returns trigger as
$$
declare
begin
    set @num = @num + NEW.a;
    return NEW;
end
$$ LANGUAGE PLPGSQL;
/
drop trigger if exists trigger_0056;
create trigger trigger_0056 before insert on t_trigger_0056 for each row execute procedure func_trigger_0056();
/
set @num := 0;
select @num;
insert into t_trigger_0056 values(100);
select @num;
insert into t_trigger_0056 values(200);
select @num;

--step3:清理环境
drop table if exists t_trigger_0056 cascade;
drop function if exists func_trigger_0056;
drop trigger if exists trigger_0056;
